package com.campusbackend.mapper;

import com.campusbackend.entity.Teachers;
import com.campusbackend.entity.vo.TeacherListVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 教师表 Mapper 接口
 * </p>
 *
 * @author yezi
 * @since 2025-07-26
 */
@Mapper
public interface TeachersMapper extends BaseMapper<Teachers> {

    /**
     * 根据工号查找教师
     * @param employeeId 工号
     * @return 教师信息
     */
    @Select("SELECT * FROM teachers WHERE employee_id = #{employeeId}")
    Teachers findByEmployeeId(@Param("employeeId") String employeeId);

    /**
     * 根据工号或姓名搜索教师
     * @param keyword 搜索关键词（工号或姓名）
     * @return 教师列表
     */
    @Select("SELECT " +
            "id as teacher_id, " +
            "employee_id, " +
            "username, " +
            "real_name, " +
            "phone, " +
            "email, " +
            "id_card, " +
            "professional_title, " +
            "personal_homepage, " +
            "avatar, " +
            "audit_status, " +
            "status, " +
            "create_time " +
            "FROM teachers " +
            "WHERE status = 1 AND audit_status = 1 " +
            "AND (employee_id LIKE CONCAT('%', #{keyword}, '%') " +
            "OR real_name LIKE CONCAT('%', #{keyword}, '%')) " +
            "ORDER BY create_time DESC")
    List<TeacherListVO> searchTeachers(@Param("keyword") String keyword);

    /**
     * 检查工号是否已存在
     * @param employeeId 工号
     * @return 存在的记录数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE employee_id = #{employeeId}")
    int countByEmployeeId(String employeeId);
    
    @Select("SELECT COUNT(*) FROM teachers WHERE id_card = #{idCard}")
    int countByIdCard(String idCard);

    @Select("SELECT COUNT(*) FROM teachers WHERE phone = #{phone}")
    int countByPhone(String phone);

    @Select("SELECT COUNT(*) FROM teachers WHERE email = #{email}")
    int countByEmail(String email);

    /**
     * 检查手机号是否被其他用户占用（排除当前用户）
     * @param phone 手机号
     * @param employeeId 当前用户工号
     * @return 存在的记录数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE phone = #{phone} AND employee_id != #{employeeId}")
    int countByPhoneExcludeCurrentUser(String phone, String employeeId);

    /**
     * 检查邮箱是否被其他用户占用（排除当前用户）
     * @param email 邮箱
     * @param employeeId 当前用户工号
     * @return 存在的记录数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE email = #{email} AND employee_id != #{employeeId}")
    int countByEmailExcludeCurrentUser(String email, String employeeId);

    /**
     * 检查身份证号是否被其他用户占用（排除当前用户）
     * @param idCard 身份证号
     * @param employeeId 当前用户工号
     * @return 存在的记录数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE id_card = #{idCard} AND employee_id != #{employeeId}")
    int countByIdCardExcludeCurrentUser(String idCard, String employeeId);

    /**
     * 获取待审核的教师列表
     * @return 待审核教师列表
     */
    @Select("SELECT * FROM teachers WHERE audit_status = 0 ORDER BY create_time DESC")
    List<Teachers> getPendingAuditTeachers();

    /**
     * 获取所有教师（用于管理）
     * @return 所有教师列表
     */
    @Select("SELECT * FROM teachers WHERE audit_status !=0 ORDER BY create_time DESC ")
    List<Teachers> getAllTeachersForManagement();

    /**
     * 根据审核状态统计教师数量
     * @param auditStatus 审核状态
     * @return 教师数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE audit_status = #{auditStatus}")
    long countByAuditStatus(@Param("auditStatus") Byte auditStatus);

    /**
     * 根据账号状态统计教师数量
     * @param status 账号状态
     * @return 教师数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE status = #{status}")
    long countByStatus(@Param("status") Byte status);

    /**
     * 统计今日新增教师数量
     * @return 今日新增教师数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE DATE(create_time) = CURDATE()")
    long countTodayNew();

    /**
     * 统计本月新增教师数量
     * @return 本月新增教师数量
     */
    @Select("SELECT COUNT(*) FROM teachers WHERE YEAR(create_time) = YEAR(NOW()) AND MONTH(create_time) = MONTH(NOW())")
    long countMonthNew();

    /**
     * 更新教师审核状态
     * @param id 教师ID
     * @param auditStatus 审核状态
     * @param auditReason 审核备注
     * @return 更新的记录数
     */
    @Update("UPDATE teachers SET audit_status = #{auditStatus}, audit_reason = #{auditReason}, audit_time = NOW() WHERE id = #{id}")
    int updateAuditStatus(@Param("id") Long id, @Param("auditStatus") Byte auditStatus, @Param("auditReason") String auditReason);

    /**
     * 更新教师审核状态和账号状态
     * @param id 教师ID
     * @param auditStatus 审核状态
     * @param status 账号状态
     * @param auditReason 审核备注
     * @return 更新的记录数
     */
    @Update("UPDATE teachers SET audit_status = #{auditStatus}, status = #{status}, audit_reason = #{auditReason}, audit_time = NOW(), update_time = NOW() WHERE id = #{id}")
    int updateAuditAndAccountStatus(@Param("id") Long id, @Param("auditStatus") Byte auditStatus, @Param("status") Byte status, @Param("auditReason") String auditReason);

    /**
     * 获取被驳回的教师列表
     * @return 被驳回教师列表
     */
    @Select("SELECT * FROM teachers WHERE audit_status = 2 ORDER BY audit_time DESC")
    List<Teachers> getRejectedTeachers();

    /**
     * 获取被注销的教师列表
     * @return 被注销教师列表
     */
    @Select("SELECT * FROM teachers WHERE status = 0 and audit_status =1 ORDER BY update_time DESC")
    List<Teachers> getDeactivatedTeachers();

    /**
     * 根据关键词搜索教师（用于管理功能，返回完整实体）
     * @param keyword 搜索关键词
     * @return 教师列表
     */
    @Select("SELECT * FROM teachers WHERE real_name LIKE CONCAT('%', #{keyword}, '%') OR employee_id LIKE CONCAT('%', #{keyword}, '%') ORDER BY create_time DESC")
    List<Teachers> searchTeachersForManagement(@Param("keyword") String keyword);

    /**
     * 根据工号查找教师详情
     * @param employeeId 工号
     * @return 教师信息
     */
    @Select("SELECT * FROM teachers WHERE employee_id = #{employeeId}")
    Teachers findByEmployeeIdForDetail(@Param("employeeId") String employeeId);

    /**
     * 按日期统计新增教师数量（最近N天）
     * @param days 天数
     * @return 每日新增教师统计
     */
    @Select("SELECT DATE(create_time) as date, COUNT(*) as count " +
            "FROM teachers " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL #{days} DAY) " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date DESC")
    List<Map<String, Object>> getDailyNewTeachersCount(@Param("days") int days);

}
